package com.yinggu.dao;

import com.yinggu.pojo.KUser;
import com.yinggu.utils.DBUtil;
import com.yinggu.utils.Page;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

public class UserDao {
    public KUser login(String userNo,String pwd) throws SQLException {
        String sql = "SELECT *FROM k_user WHERE userNo=? AND pwd=?";
        return (KUser) DBUtil.query(new BeanHandler(KUser.class),sql,userNo,pwd);
    }
    public int addUser(KUser user) {
        String sql = "INSERT INTO k_user(userNo,pwd,userName,deptNo,sex,age) VALUES(?,?,?,?,?,?)";
        Object[] params = { user.getUserNo(),user.getPwd(),user.getUserName(),user.getDeptNo(),user.getSex(),user.getAge() };
        return DBUtil.update(sql,params);
    }
    public int editUser(KUser user) {
        String sql = "UPDATE k_user SET userNo=?,userName=?,deptNo=?,sex=?,age=? WHERE id=?";
        Object[] params = { user.getUserNo(),user.getUserName(),user.getDeptNo(),user.getSex(),user.getAge(),user.getId() };
        return DBUtil.update(sql,params);
    }
    public int removeUser(int id) {
        String sql = "DELETE FROM k_user WHERE id=?";
        return DBUtil.update(sql,id);
    }
    public List<KUser> getUsers(String key, Page page) throws SQLException {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT u.ID,u.USERNO,u.USERNAME,u.PWD,u.DEPTNO,u.SEX,u.AGE,u.POSITION,u.HOMETOWN,u.EMAIL,u.ROLENO,u.REMARK,d.deptName FROM k_user u,k_dept d WHERE u.deptNo=d.deptNo ");
        if (key != null && key != "") {
            sb.append(" And u.userName like '%"+key+"%' OR u.userNo like '%"+key+"%' ");
        }
        sb.append(" limit "+(page.getCurrentPage()-1)*page.getPageSize()+","+page.getPageSize());
        return (List<KUser>)DBUtil.query(new BeanListHandler(KUser.class), sb.toString());
    }
    public int getCount(String key) throws SQLException {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT count(*) first FROM k_user u,k_dept d WHERE u.deptNo=d.deptNo ");
        if (key != null && key != "") {
            sb.append(" AND userName like '%"+key+"%' OR userNo like '%"+key+"%' ");
        }
        final Object first = DBUtil.first(sb.toString());
        return Integer.parseInt(first.toString());
    }
    public KUser getUser(int id) throws SQLException {
        String sql = "SELECT *FROM k_user WHERE id=?";
        return (KUser)DBUtil.query(new BeanHandler(KUser.class), sql, id);
    }

}
